package zhongbo.java.air.monitor.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import zhongbo.java.air.monitor.model.FlightActivityDaily;

import java.util.Date;
import java.util.List;

/**
 * 说明：
 *
 * @author 钟波dellNotebook
 * @since 2025年04月08日 星期二 03:24:47
 */
@Mapper
public interface FlightActivityDailyMapper extends BaseMapper<FlightActivityDaily> {

    /**
     * 查询当天分组统计结果
     *
     * @param date 某一天的日期 如: 2025-04-08
     * @return 当天分组统计结果
     */
    @Select("SELECT \n" +
            "    a.icao_code AS icaoCode,\n" +
            "    MIN(a.timestamp) AS firstSeenTime,\n" +
            "    MAX(a.timestamp) AS lastSeenTime,\n" +
            "    DATE(MIN(a.timestamp)) AS dat,\n" +
            "    (\n" +
            "        SELECT altitude \n" +
            "        FROM air_flight_path \n" +
            "        WHERE icao_code = a.icao_code \n" +
            "        ORDER BY timestamp DESC \n" +
            "        LIMIT 1\n" +
            "    ) AS lastAltitude," +
            "    (\n" +
            "        SELECT latitude \n" +
            "        FROM air_flight_path \n" +
            "        WHERE icao_code = a.icao_code \n" +
            "        ORDER BY timestamp DESC \n" +
            "        LIMIT 1\n" +
            "    ) AS lastLatitude,\n" +
            "    (\n" +
            "        SELECT longitude \n" +
            "        FROM air_flight_path \n" +
            "        WHERE icao_code = a.icao_code \n" +
            "        ORDER BY timestamp DESC \n" +
            "        LIMIT 1\n" +
            "    ) AS lastLongitude,\n" +
            "    (\n" +
            "        SELECT speed \n" +
            "        FROM air_flight_path \n" +
            "        WHERE icao_code = a.icao_code \n" +
            "        ORDER BY timestamp DESC \n" +
            "        LIMIT 1\n" +
            "    ) AS lastSpeed,\n" +
            "    (\n" +
            "        SELECT heading \n" +
            "        FROM air_flight_path \n" +
            "        WHERE icao_code = a.icao_code \n" +
            "        ORDER BY timestamp DESC \n" +
            "        LIMIT 1\n" +
            "    ) AS lastHeading,\n" +
            "    COUNT(*) AS occurrenceCount\n" +
            "FROM air_flight_path a\n" +
            "WHERE DATE(timestamp) = #{date}\n" +
            "GROUP BY icao_code;")
    List<FlightActivityDaily> selectDailyStats(String date);

    /**
     * 根据日期查询当天的统计结果
     *
     * @param date 日期
     * @return 当天的统计结果
     */
    @Select("SELECT * FROM air_flight_activity_daily WHERE date = #{date}")
    List<FlightActivityDaily> getDailyStats(String date);

    /**
     * 根据 icaoCode 和 date 查询记录
     *
     * @param icaoCode ICAO 24位地址码
     * @param date 统计日期
     * @return 查询结果
     */
    @Select("SELECT * FROM air_flight_activity_daily WHERE icao_code = #{icaoCode} AND date = DATE(#{date})")
    List<FlightActivityDaily> selectByIcaoAndDate(@Param("icaoCode") String icaoCode, @Param("date") Date date);

    /**
     * 查询指定行数据
     *
     * @param offset 偏移量
     * @param pageSize         大小
     * @return 对象列表
     */
    @Select("SELECT * FROM air_flight_activity_daily ORDER BY id LIMIT #{pageSize} OFFSET #{offset}")
    List<FlightActivityDaily> queryAllByLimit(@Param("offset") int offset, @Param("pageSize") int pageSize);

    /**
     * 根据 icaoCode 查询飞机字典表信息
     *
     * @param icaoCode ICAO 24位地址码
     * @return 飞机字典表信息
     */
    @Select("SELECT registration, airline, aircraft_type AS aircraftType FROM air_aircraft WHERE icao_code = #{icaoCode}")
    FlightActivityDaily selectAircraftInfoByIcao(@Param("icaoCode") String icaoCode);
}
